﻿
namespace SistemaLogistica.DataAccess
{
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using SistemaLogistica.Business.Entities;

    public class SucursalData : BaseDataAccess
    {
        private SucursalData() { }

        private readonly static SucursalData instancia = new SucursalData();

        public static SucursalData Instancia
        {
            get { return instancia; }
        }

        public List<Sucursal> Listar()
        {
            List<Sucursal> sucursales = new List<Sucursal>();
            DbCommand command = db.GetStoredProcCommand("GetAllFromSucursal");

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    sucursales.Add(new Sucursal
                    {
                        Direccion = dr.GetString(dr.GetOrdinal("Direccion")),
                        EsPrincipal = dr.GetBoolean(dr.GetOrdinal("EsPrincipal")),
                        IdSucursal = dr.GetInt32(dr.GetOrdinal("IdSucursal")),
                        Telefono = dr.GetString(dr.GetOrdinal("Telefono")),
                        Ubigeo = UbigeoData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdUbigeo")))
                    });
                }
            }
            return sucursales;
        }

        public List<Sucursal> Buscar(string nombre)
        {
            List<Sucursal> sucursales = new List<Sucursal>();
            DbCommand command = db.GetStoredProcCommand("SearchSucursal");
            db.AddInParameter(command, "direccion", DbType.String, nombre);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    sucursales.Add(new Sucursal
                    {
                        Direccion = dr.GetString(dr.GetOrdinal("Direccion")),
                        EsPrincipal = dr.GetBoolean(dr.GetOrdinal("EsPrincipal")),
                        IdSucursal = dr.GetInt32(dr.GetOrdinal("IdSucursal")),
                        Telefono = dr.GetString(dr.GetOrdinal("Telefono")),
                        Ubigeo = UbigeoData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdUbigeo")))
                    });
                }
            }
            return sucursales;
        }

        public List<Sucursal> ListarPorUbigeo(int ubigeoID)
        {
            List<Sucursal> sucursales = new List<Sucursal>();
            DbCommand command = db.GetStoredProcCommand("GetSucursalByUbigeo");
            db.AddInParameter(command, "idUbigeo", DbType.Int32, ubigeoID);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    sucursales.Add(new Sucursal
                    {
                        Direccion = dr.GetString(dr.GetOrdinal("Direccion")),
                        EsPrincipal = dr.GetBoolean(dr.GetOrdinal("EsPrincipal")),
                        IdSucursal = dr.GetInt32(dr.GetOrdinal("IdSucursal")),
                        Telefono = dr.GetString(dr.GetOrdinal("Telefono")),
                        Ubigeo = UbigeoData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdUbigeo")))
                    });
                }
            }
            return sucursales;
        }

        public Sucursal ObtenerPorID(int sucursalID)
        {
            Sucursal sucursal = null;
            DbCommand command = db.GetStoredProcCommand("GetSucursalByIdSucursal");
            db.AddInParameter(command, "idSucursal", DbType.Int32, sucursalID);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    sucursal = new Sucursal
                    {
                        Direccion = dr.GetString(dr.GetOrdinal("Direccion")),
                        EsPrincipal = dr.GetBoolean(dr.GetOrdinal("EsPrincipal")),
                        IdSucursal = dr.GetInt32(dr.GetOrdinal("IdSucursal")),
                        Telefono = dr.GetString(dr.GetOrdinal("Telefono")),
                        Ubigeo = UbigeoData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdUbigeo")))
                    };
                }
            }
            return sucursal;
        }

        public bool Agregar(Sucursal sucursal)
        {
            DbCommand command = db.GetStoredProcCommand("InsertSucursal");
            db.AddInParameter(command, "direccion", DbType.String, sucursal.Direccion);
            db.AddInParameter(command, "esPrincipal", DbType.Boolean, sucursal.EsPrincipal);
            db.AddOutParameter(command, "idSucursal", DbType.String, 11);
            db.AddInParameter(command, "idUbigeo", DbType.Int32, sucursal.Ubigeo.IdUbigeo);
            db.AddInParameter(command, "telefono", DbType.String, sucursal.Telefono);

            int result = db.ExecuteNonQuery(command);

            if (result != 0) return true;
            else return false;
        }

        public bool Modificar(Sucursal sucursal)
        {
            DbCommand command = db.GetStoredProcCommand("UpdateSucursal");
            db.AddInParameter(command, "direccion", DbType.String, sucursal.Direccion);
            db.AddInParameter(command, "esPrincipal", DbType.Boolean, sucursal.EsPrincipal);
            db.AddInParameter(command, "idSucursal", DbType.String, sucursal.IdSucursal);
            db.AddInParameter(command, "idUbigeo", DbType.Int32, sucursal.Ubigeo.IdUbigeo);
            db.AddInParameter(command, "telefono", DbType.String, sucursal.Telefono);

            int result = db.ExecuteNonQuery(command);

            if (result != 0) return true;
            else return false;
        }

        public void Eliminar(int sucursalID)
        {
            DbCommand command = db.GetStoredProcCommand("DeleteSucursal");
            db.AddInParameter(command, "idSucursal", DbType.Int32, sucursalID);
            db.ExecuteNonQuery(command);
        }

        public bool ExistePrincipal()
        {
            Sucursal sucursal = null;
            DbCommand command = db.GetStoredProcCommand("GetSucursalPrincipal");
            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    sucursal = new Sucursal
                    {
                        Direccion = dr.GetString(dr.GetOrdinal("Direccion")),
                        EsPrincipal = dr.GetBoolean(dr.GetOrdinal("EsPrincipal")),
                        IdSucursal = dr.GetInt32(dr.GetOrdinal("IdSucursal")),
                        Telefono = dr.GetString(dr.GetOrdinal("Telefono"))
                    };
                }
            }
            if (sucursal != null)
                return true;
            else
                return false;
        }
    }
}
